package com.qiudao.sqlhelper.generator;

import com.qiudao.sqlhelper.data.Column;
import com.qiudao.sqlhelper.data.Table;
import com.qiudao.sqlhelper.util.NumberUtil;
import com.qiudao.sqlhelper.util.ResourceUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.InputStream;

/**
 * Description:  
 * @author: gdc
 * @date: 2019/9/1
 * @version 1.0
 */
public class ExcelTableConfigReader {

    private static final Logger logger = LoggerFactory.getLogger(ExcelTableConfigReader.class);

    private static final int COLUMN_START_ROW = 2;

    /**
     * 读取表结构Excel定义文件，并转化为相应的SQL
     *
     * @param resource  文件全路径
     * @param sheetName sheet名称
     * @return
     * @throws Exception
     */
    public static Table parseTableDefineFromFile(String resource, String sheetName) throws Exception {
        if (StringUtils.isBlank(resource) || StringUtils.isBlank(sheetName)) {
            throw new Exception("配置文件不能为空");
        }

        Workbook wb = createWorkbook(new File(resource));
        if (wb == null) {
            logger.error("can't read excel from configLocation!");
            throw new Exception("无法解析配置文件:" + resource);
        }

        return parseResource(sheetName, wb);
    }

    /**
     * 读取表结构Excel定义文件，并转化为相应的SQL
     *
     * @param configLocation classpath下的文件路径
     * @param sheetIndex     sheet序号，从1开始
     * @return
     * @throws Exception
     */
    public static Table parseTableDefine(String configLocation, int sheetIndex) throws Exception {
        if (StringUtils.isBlank(configLocation) || sheetIndex < 0) {
            throw new Exception("配置文件不能为空");
        }

        InputStream is = ResourceUtils.getResourceAsStream(configLocation);
        Workbook wb = createWorkbook(is);
        if (wb == null) {
            logger.error("can't read excel from configLocation!");
            throw new Exception("无法解析配置文件:" + configLocation);
        }

        return parseResource(sheetIndex, wb);
    }

    /**
     * 读取表结构Excel定义文件，并转化为相应的SQL
     *
     * @param configLocation classpath下的文件路径
     * @param sheetName      sheetName
     * @return
     * @throws Exception
     */
    public static Table parseTableDefine(String configLocation, String sheetName) throws Exception {
        if (StringUtils.isBlank(configLocation) || StringUtils.isBlank(sheetName)) {
            throw new Exception("配置文件不能为空");
        }

        InputStream is = ResourceUtils.getResourceAsStream(configLocation);
        Workbook wb = createWorkbook(is);
        if (wb == null) {
            logger.error("can't read excel from configLocation!");
            throw new Exception("无法解析配置文件:" + configLocation);
        }

        return parseResource(sheetName, wb);
    }

    /**
     * 通过工作簿和给定页名称，解析表信息
     * @param sheetName
     * @param wb
     * @return
     * @throws Exception
     */
    private static Table parseResource(String sheetName, Workbook wb) throws Exception {
        Sheet sheet = wb.getSheet(sheetName);
        if (sheet == null) {
            throw new Exception("sheetName:" + sheetName + " 无法读取!");
        }

        // 获取当前页的最大行数
        int totalRows = sheet.getLastRowNum();
        if (totalRows < 4) {
            throw new Exception("配置文件行数不正确!");
        }
        return parseTableConfig(sheet);
    }

    private static Table parseResource(int sheetIndex, Workbook wb) throws Exception {
        if (sheetIndex > wb.getNumberOfSheets()) {
            logger.error("sheetIndex 超出范围!");
            throw new Exception("sheetIndex:" + sheetIndex + " 超出范围!");
        }

        Sheet sheet = wb.getSheetAt(sheetIndex);
        if (sheet == null) {
            throw new Exception("sheetIndex:" + sheetIndex + " 无法读取!");
        }

        int totalRows = sheet.getLastRowNum();
        if (totalRows < 4) {
            throw new Exception("配置文件行数不正确!");
        }

        return parseTableConfig(sheet);
    }

    /**
     * 通过给定页（Sheet） 获取表昵称和物理表名
     * @param sheet
     * @return
     * @throws Exception
     */
    private static Table parseTableConfig(Sheet sheet) throws Exception {
        Table table = parseTableName(sheet);
        if (table == null) {
            throw new Exception("表名称配置不正确!");
        }

        parseColumns(table, sheet);
        return table;
    }

    private static Table parseTableName(Sheet sheet) {
        // 读取表名称信息
        Row row = sheet.getRow(0);
        if (row == null) {
            return null;
        }

        Cell tableNameCell = row.getCell(1);
        Cell physicalNameCell = row.getCell(3);
        if (tableNameCell == null || physicalNameCell == null) {
            return null;
        }

        String tableName = tableNameCell.getStringCellValue();
        String physicalName = physicalNameCell.getStringCellValue();
        if (StringUtils.isBlank(tableName) || StringUtils.isBlank(physicalName)) {
            return null;
        }
        Table table = new Table();
        table.setTableName(tableName);
        table.setPhysicalName(physicalName);
        return table;
    }

    /**
     * 编号 名称 物理名 类型 是否主键 是否外键 NOT NULL 备注
     *
     * @param table 表名
     * @param sheet 单元格
     */
    private static void parseColumns(Table table, Sheet sheet) {
        for (int i = COLUMN_START_ROW; i <= sheet.getLastRowNum(); i++) {
            Row columnDefine = sheet.getRow(i);
            Column column = parseColumn(columnDefine);
            table.addColumn(column);
        }
    }

    /**
     * 通过给定行和列索引获取 int类型的列值
     * @param row           行
     * @param columnIndex   列索引
     * @return              结果
     */
    private static int getColumnIntValue(Row row, int columnIndex) {
        Cell cell = row.getCell(columnIndex);
        if (cell == null) {
            return 0;
        }
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            Double d = cell.getNumericCellValue();
            return d.intValue();
        } else {
            String str = cell.getStringCellValue();
            return NumberUtil.parseInt(str, 0);
        }
    }

    /**
     * 通过给定行和列索引获取 String 类型的列值
     * @param row           行
     * @param columnIndex   列索引
     * @return              结果
     */
    private static String getColumnStringValue(Row row, int columnIndex) {
        Cell cell = row.getCell(columnIndex);
        if (cell == null) {
            return StringUtils.EMPTY;
        }
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            Double d = cell.getNumericCellValue();
            return String.valueOf(d.doubleValue());
        } else {
            return StringUtils.trim(cell.getStringCellValue());
        }
    }

    /**
     * 通过给定行解析行中的列信息
     * @param columnDefine      行信息
     * @return                  列信息
     */
    private static Column parseColumn(Row columnDefine) {
        Column column = new Column();
        int columnIndex = 0;
        column.setSortNum(columnDefine.getRowNum() - COLUMN_START_ROW);
        column.setSortCode(getColumnIntValue(columnDefine, columnIndex++));
        column.setAliasName(getColumnStringValue(columnDefine, columnIndex++));
        column.setColumnName(getColumnStringValue(columnDefine, columnIndex++));
        column.setColumType(getColumnStringValue(columnDefine, columnIndex++));
        column.setPrimary(isSelect(getColumnStringValue(columnDefine, columnIndex++)));
        column.setForeign(isSelect(getColumnStringValue(columnDefine, columnIndex++)));
        column.setNotNull(isSelect(getColumnStringValue(columnDefine, columnIndex++)));
        column.setComment(column.getAliasName() + " " + getColumnStringValue(columnDefine, columnIndex++));
        column.setNew(isSelect(getColumnStringValue(columnDefine, columnIndex++)));
        return column;
    }

    /**
     * 判断是否被选择
     * @param val   传入值
     * @return      true or false
     */
    private static boolean isSelect(String val) {
        val = StringUtils.trim(val);
        return StringUtils.equalsIgnoreCase(val, Column.SELECT);
    }

    /**
     * 通过给定的输入流创建工作簿对象
     * @param is        给定流
     * @return          工作簿对象
     */
    private static Workbook createWorkbook(InputStream is) {
        Workbook wb = null;
        try {
            wb = WorkbookFactory.create(is);
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
        return wb;
    }

    /**
     * 通过给定的文件创建工作簿对象
     * @param file      给定文件
     * @return          工作簿对象
     */
    private static Workbook createWorkbook(File file) {
        Workbook wb = null;
        try {
            wb = WorkbookFactory.create(file);
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
        return wb;
    }
}
